﻿using EasyDater.Base;
using EasyDater.Built;
using EasyDater.Helper;
using System.Data;
using static EasyDater.Base.EnumType;

//产品主表，明细表插入
namespace EasyDater
{
    public class BuiltEntity
    {

        BuiltSQL builtSQL = new BuiltSQL();
        BuiltCore builtCore = new BuiltCore();

        #region  向单独一个表中插入数据
        //public bool InsertTable<T>(T t, string sql)  附带执行一些SQL语句

        //插入实体类
        public string InsertTable<T>(T t)
        {
            if (t is null) return "实体类为空.";
            Type clsType = typeof(T);
            string mainTable = clsType.Name;//类名就是表名

            MMTP mmtp = new MMTP();
            int k = 0;
            foreach (System.Reflection.PropertyInfo p in t.GetType().GetProperties())
            {
                var fieldName = p.Name;//字段名称
                //排除自增列，不允许插入
                if (k == 0)
                {
                    string strFieldDescription = TableInfo.GetFieldDescription<T>(fieldName);
                    if (strFieldDescription == EnumFieldType.AUTOKEY.ToString())
                    {
                        k++;
                        continue;
                    }
                }
                object? fieldObj = p.GetValue(t, null);//字段值
                if (fieldObj is null) { continue; }


                string fieldType = builtCore.GetEnumValue(p.PropertyType.Name);
                //字段类型         
                mmtp.AddItems(fieldName, fieldObj.ToString(), fieldType);
            }

            List<string[]> mainList = mmtp.Items;

            return builtCore.InsertTable(mainTable, mainList);
        }

        #endregion

        #region 根据实体类删除一条记录

        public string DeleteTable<T>(T t)
        {
            if (t is null) return "实体类为空.";
            Type clsType = typeof(T);
            string mainTable = clsType.Name;//类名就是表名
            string fieldType = string.Empty;//字段类型
            var fieldName = string.Empty;//字段名字
            object? fieldObj = null;
            foreach (System.Reflection.PropertyInfo p in t.GetType().GetProperties())
            {

                fieldName = p.Name;//字段名称
                //if (string.IsNullOrEmpty(fieldName)) return "无主键.";

                string? strFieldDescription = TableInfo.GetFieldDescription<T>(fieldName);
                if (strFieldDescription is null)
                { continue; }
                else if (strFieldDescription == EnumFieldType.AUTOKEY.ToString() || strFieldDescription == EnumFieldType.PRIMARYKEY.ToString())
                {
                    fieldObj = p.GetValue(t, null);//字段值
                    fieldType = builtCore.GetEnumValue(p.PropertyType.Name);
                    break;
                }
                else
                   continue;
            }
            
            string? value = Convert.ToString(fieldObj);
            if (value is null)
            {
                if (fieldType == global::EasyDater.Base.EnumType.SimInType.A.ToString() || fieldType == global::EasyDater.Base.EnumType.SimInType.S.ToString())
                    value = "";
                else
                    value = "0";
            }
            if (string.IsNullOrEmpty(value) || value == "0") return "主键值为空.";

            bool flag = false;
            switch (fieldType)
            {
                case "S":
                    flag = builtSQL.DeleteTable(mainTable, fieldName, value);
                    break;

                case "D":
                    flag = builtSQL.DeleteTable(mainTable, fieldName, Int32.Parse(value));
                    break;
                default:
                    flag = builtSQL.DeleteTable(mainTable, fieldName, value);
                    break;
            }

            if (flag)
                return "删除成功.";
            else
                return "删除失败.";

        }
        #endregion

        #region 根据实体类的主键值，获取实体类的全部信息

        public MSTP? GetOneMSTP<T>(T t)
        {
            if (t is null) return null;
            Type clsType = typeof(T);
            string mainTable = clsType.Name;//类名就是表名

            //获取主键开始  名称 值  单字符类型
            string fieldType = string.Empty;//字段类型
            var fieldName = string.Empty;//字段名字
            object? fieldObj = null;
            foreach (System.Reflection.PropertyInfo p in t.GetType().GetProperties())
            {

                fieldName = p.Name;//字段名称
                //if (string.IsNullOrEmpty(fieldName)) return "无主键.";

                string? strFieldDescription = TableInfo.GetFieldDescription<T>(fieldName);
                if (strFieldDescription is null)
                { continue; }
                else if (strFieldDescription == EnumFieldType.AUTOKEY.ToString() || strFieldDescription == EnumFieldType.PRIMARYKEY.ToString())
                {
                    fieldObj = p.GetValue(t, null);//字段值
                    fieldType = builtCore.GetEnumValue(p.PropertyType.Name);//字段类型
                    break;
                }
                else
                    continue;

            }

            string? value = Convert.ToString(fieldObj);
            if (value is null)
            {
                if (fieldType == global::EasyDater.Base.EnumType.SimInType.A.ToString() || fieldType == global::EasyDater.Base.EnumType.SimInType.S.ToString())
                    value = "";
                else
                    value = "0";
            }
            if (string.IsNullOrEmpty(value) || value == "0") return null;
            MSTP mSTP=new MSTP();
            mSTP.AddItems(fieldName, value, fieldType);
            //获取主键结束
            return mSTP;
        }

       
        #endregion

        //下次从这个方法开始测试
        #region  更新一个表中数据


        //传入实体类，更新表
        public string UpdateTable<T>(T t)
        {
            if (t is null) return "实体类为空.";
            Type clsType = typeof(T);
            string mainTable = clsType.Name;//类名就是表名


            MMTP mmtp = new MMTP();
            MSTP mstp = new MSTP();
            foreach (System.Reflection.PropertyInfo p in t.GetType().GetProperties())
            {

                object? fieldObj = p.GetValue(t, null);//字段的值
                var fieldName = p.Name;//字段名称
                string fieldType = builtCore.GetEnumValue(p.PropertyType.Name);//字段类型  
                //主键是用来修改的条件
                string? strFieldDescription = TableInfo.GetFieldDescription<T>(fieldName);
                if (strFieldDescription == EnumFieldType.AUTOKEY.ToString() || strFieldDescription == EnumFieldType.PRIMARYKEY.ToString())
                {
                    if (string.IsNullOrEmpty(fieldName)) return "无主键.";
                    string? value = Convert.ToString(fieldObj);
                    if (value is null)
                    {
                        if (fieldType == global::EasyDater.Base.EnumType.SimInType.A.ToString() || fieldType == global::EasyDater.Base.EnumType.SimInType.S.ToString())
                            value = "";
                        else
                            value = "0";
                    }

                    mstp.AddItems(fieldName, value, fieldType);
                    continue;
                }
                else if (strFieldDescription == EnumFieldType.DEFAULTCOL.ToString() || strFieldDescription == EnumFieldType.COMPUTECOL.ToString())
                {
                    continue;
                }
                else
                {
                    string? value = Convert.ToString(fieldObj);
                    if (value is null)
                    {
                        if (fieldType == global::EasyDater.Base.EnumType.SimInType.A.ToString() || fieldType == global::EasyDater.Base.EnumType.SimInType.S.ToString())
                            value = "";
                        else
                            value = "0";
                    }




                    mmtp.AddItems(fieldName, value, fieldType);
                }

            }
            if (mstp is null) { return "无主键数据."; }
            List<string[]> mainList = mmtp.Items;
            return builtCore.UpdateTable(mainTable, mainList, mstp.Items);


        }

        #endregion

        //同时插入多个表的数据
        public string InsertMuTable(List<String[,]> detailList)
        {
            string result = "";
            string strDetailSQL = "";
            int count = detailList.Count;
            for (int j = 0; j < count; j++)
            {
                string subKey = "";
                string subValue = "";
                string[,] item = detailList[j];


                for (int i = 1; i < item.GetLength(0); i++)
                {

                    subKey += item[i, 0].ToString();//键
                    ////N是整数 D是decimal
                    if (item[i, 2].ToString() == "N")//类型
                        subValue += item[i, 1].ToString();//值
                    else if (item[i, 2].ToString() == "D")
                        subValue += item[i, 1].ToString();//值
                    else if (item[i, 2].ToString() == "A")//date 的第二个字母
                        subValue += "'" + item[i, 1].ToString() + "'";//值
                    else
                        subValue += "'" + item[i, 1].Trim() + "'";
                    if (i < (item.GetLength(0) - 1))
                    {
                        subKey += ",";
                        subValue += ",";
                    }
                }
                strDetailSQL += " INSERT INTO " + item[0, 1].ToString() + " (" + subKey + ") VALUES (" + subValue + ")";
                if (j < count - 1)
                    strDetailSQL += ";";
            }



            try
            {
                if (SQLHelper.ExecuteSql(strDetailSQL))
                    result = "执行成功";
                else
                    result = "执行失败";

            }
            catch (Exception ex)
            {
                result = ex.Message.ToString();
            }
            return result;
        }

        #region 同时插入主表和明细表数据(new)
        /// <summary>
        /// 同时插入主表和明细表数据
        /// </summary>
        /// <param name="mainTable">主表的表名</param>
        /// <param name="mainList">往主表中将要插入的数据 [key,value,type]</param>
        /// <param name="detailTable">从表的表名</param>
        /// <param name="detailList">往从表中插入的数据 []</param>
        /// <returns></returns>
        public string InsertMainAndDetail(string mainTable, List<String[]> mainList, string detailTable, List<String[,]> detailList)
        {

            //第一步插入主表数据
            string result = "";
            try
            {
                string key = "";
                string value = "";
                int len = mainList.Count;

                for (int i = 0; i < len; i++)
                {
                    key += mainList[i][0].ToString();//键
                    ////N是整数 D是decimal
                    if (mainList[i][2].ToString() == "N")//类型
                        value += Int32.Parse(mainList[i][1].ToString());//值
                    else if (mainList[i][2].ToString() == "D")
                        value += Decimal.Parse(mainList[i][1].ToString());//值
                    else if (mainList[i][2].ToString() == "A")//date 的第二个字母
                        value += "'" + DateTime.Parse(mainList[i][1].ToString()) + "'";//值
                    else
                        value += "'" + mainList[i][1].Trim() + "'";
                    if (i < (len - 1))
                    {
                        key += ",";
                        value += ",";
                    }
                }
                string strMainSQL = "INSERT INTO " + mainTable + " (" + key + ") VALUES (" + value + ")";


                //组合从表的SQL语句####################################
                string strDetailSQL = "";
                int count = detailList.Count;
                for (int j = 0; j < count; j++)
                {
                    string subKey = "";
                    string subValue = "";
                    string[,] item = detailList[j];

                    //编码，数量，价格全部不能为空
                    if (item[1, 1].Length < 1)
                        continue;
                    for (int i = 0; i < item.GetLength(0); i++)
                    {

                        subKey += item[i, 0].ToString();//键
                        ////N是整数 D是decimal
                        if (item[i, 2].ToString() == "N")//类型
                            subValue += item[i, 1].ToString();//值
                        else if (item[i, 2].ToString() == "D")
                            subValue += item[i, 1].ToString();//值
                        else if (item[i, 2].ToString() == "A")//date 的第二个字母
                            subValue += "'" + item[i, 1].ToString() + "'";//值
                        else
                            subValue += "'" + item[i, 1].Trim() + "'";
                        if (i < (item.GetLength(0) - 1))
                        {
                            subKey += ",";
                            subValue += ",";
                        }
                    }
                    strDetailSQL += " INSERT INTO " + detailTable + " (" + subKey + ") VALUES (" + subValue + ")";
                    if (j < count - 1)
                        strDetailSQL += ";";
                }



                try
                {
                    if (SQLHelper.ExecuteSql(strMainSQL + ";" + strDetailSQL))
                        result = "执行成功";
                    else
                        result = "执行失败";

                }
                catch (Exception ex)
                {
                    result = ex.Message.ToString();
                }
            }
            catch (Exception ex)
            {
                result = ex.Message.ToString();
            }
            return result;

        }

        #endregion


        #region 同时更新主表和明细表
        public string UpdateMainAndDetail(string mainTable, List<String[]> mainList, string[] primaryKey, string detailTable, List<String[,]> detailList)
        {

            //1:组合更新主表的SQL语句

            int len = mainList.Count;
            string result = "";
            string keyAndValue = ""; //键=值
            for (int i = 0; i < len; i++)
            {
                string value = "";//值
                ////N是整数 D是decimal
                if (mainList[i][2].ToString() == "N" || mainList[i][2].ToString() == "D")//类型
                    value += mainList[i][1].ToString();//值 
                else
                    value += "'" + mainList[i][1].Trim() + "'";

                keyAndValue += mainList[i][0].ToString();
                keyAndValue += "=";
                keyAndValue += value;
                if (i < (len - 1))
                {
                    keyAndValue += ",";
                }
            }


            string strMainSQL = "UPDATE " + mainTable + " SET " + keyAndValue + " WHERE " + primaryKey[0] + "=";
            if (primaryKey[2] == "N" || primaryKey[2] == "D")
                strMainSQL += primaryKey[1];
            else
                strMainSQL += "'" + primaryKey[1] + "'";


            //2:组合子表的SQL语句
            //组合从表的SQL语句####################################
            string strDetailSQL = "";
            int count = detailList.Count;
            for (int j = 0; j < count; j++)
            {
                string subKey = "";
                string subValue = "";
                string[,] item = detailList[j];

                //编码，数量，价格全部不能为空
                if (item[1, 1].Length < 1 || item[5, 1].Length < 1 || item[6, 1].Length < 1)
                    continue;
                for (int i = 0; i < item.GetLength(0); i++)
                {

                    subKey += item[i, 0].ToString();//键
                    ////N是整数 D是decimal
                    if (item[i, 2].ToString() == "N")//类型
                        subValue += item[i, 1].ToString();//值
                    else if (item[i, 2].ToString() == "D")
                        subValue += item[i, 1].ToString();//值
                    else if (item[i, 2].ToString() == "A")//date 的第二个字母
                        subValue += "'" + item[i, 1].ToString() + "'";//值
                    else
                        subValue += "'" + StringUtility.HTMLEncode(item[i, 1].Trim()) + "'";
                    if (i < (item.GetLength(0) - 1))
                    {
                        subKey += ",";
                        subValue += ",";
                    }
                }
                strDetailSQL += " INSERT INTO " + detailTable + " (" + subKey + ") VALUES (" + subValue + ")";
                if (j < count - 1)
                    strDetailSQL += ";";
            }


            //3:组合删除从表SQL语句-----先删除旧数据，然后插入新数据
            string strOldSQL = "delete " + detailTable + " WHERE " + primaryKey[0] + "=";
            if (primaryKey[2] == "N" || primaryKey[2] == "D")
                strOldSQL += primaryKey[1];
            else
                strOldSQL += "'" + primaryKey[1] + "'";


            //4：执行SQL语句（带有事务）
            try
            {
                if (SQLHelper.ExecuteSql(strMainSQL + ";" + strOldSQL + ";" + strDetailSQL))
                    result = "执行成功";
                else
                    result = "执行失败";
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;

            //从表
            // Logger.isInfo(strMainSQL + ";" + strDetailSQL);
        }
        #endregion

        #region 同时插入主表和明细表数据
        /// <summary>
        /// 同时插入主表和明细表数据
        /// </summary>
        /// <param name="mainTable">主表的表名</param>
        /// <param name="mainList">往主表中将要插入的数据 [key,value,type]</param>
        /// <param name="detailTable">从表的表名</param>
        /// <param name="fCode">从表中主表的主键名称（从表的外键）</param>
        /// <param name="detailList">往从表中插入的数据 []</param>
        /// <returns></returns>
        public string InsertMainAndDetail(string mainTable, List<String[]> mainList, string detailTable, string fCode, List<String[]> detailList)
        {

            //第一步插入主表数据

            string key = "";
            string value = "";
            int len = mainList.Count;

            for (int i = 0; i < len; i++)
            {
                key += mainList[i][0].ToString();//键
                ////N是整数 D是decimal
                if (mainList[i][2].ToString() == "N")//类型
                    value += Int32.Parse(mainList[i][1].ToString());//值
                else if (mainList[i][2].ToString() == "D")
                    value += Decimal.Parse(mainList[i][1].ToString());//值
                else if (mainList[i][2].ToString() == "A")//date 的第二个字母
                    value += "'" + DateTime.Parse(mainList[i][1].ToString()) + "'";//值
                else
                    value += "'" + StringUtility.HTMLEncode(mainList[i][1].Trim()) + "'";
                if (i < (len - 1))
                {
                    key += ",";
                    value += ",";
                }
            }
            string strMainSQL = "INSERT INTO " + mainTable + " (" + key + ") VALUES (" + value + ")";


            //从表
            string strDetailSQL = "";
            int count = detailList.Count;
            for (int i = 0; i < count; i++)//逐条插入数据
            {
                strDetailSQL += "Insert Into " + detailTable + " (" + fCode + ",ProductType,Num,Price,Unit,Profile) Values ('" + detailList[i][0] + "','" + StringUtility.HTMLEncode(detailList[i][1]) + "'," + Int32.Parse(detailList[i][2].Trim()) + "," + decimal.Parse(detailList[i][3].Trim()) + ",'" + StringUtility.HTMLEncode(detailList[i][4]) + "','" + StringUtility.HTMLEncode(detailList[i][5]) + "')";
                if (i < (count - 1))
                    strDetailSQL += ";";
            }

            string result = "";
            try
            {
                if (SQLHelper.ExecuteSql(strMainSQL + ";" + strDetailSQL))
                    result = "执行成功";

            }
            catch //(Exception ex)
            {
                result = "添加数据错误";
            }

            return result;

        }

        #endregion





    }
}